package cn.work.dao;

import cn.work.bean.reservationBean;
import cn.work.bean.userbean;
import cn.work.util.DataBaseutil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class reservaDao {
    public List<reservationBean> getAll() throws SQLException {
        List<reservationBean> list = new ArrayList<>();
        Connection connection = DataBaseutil.getConnection();
        if(connection != null) {
            System.out.println("数据库连接成功获取！");
            try {
                Statement statement = connection.createStatement();
                String sql = """
    SELECT a.id, u.username, a.state, b.name AS meirongshi_name, s.name AS serviceName, 
           a.service_date, a.service_period, a.user_tel, a.remark, a.fullName
    FROM appointment a
    LEFT JOIN user u ON a.user_id = u.uid
    LEFT JOIN service s ON a.service_id = s.id
    LEFT JOIN beau b ON a.beau_id = b.id
    """;
                ResultSet rs = statement.executeQuery(sql);
                System.out.println("开始查询数据...");
                if (!rs.isBeforeFirst()) { // 检查是否有结果集
                    System.out.println("SQL 查询结果为空！");
                    return list; // 返回空列表
                } else {
                    System.out.println("SQL 查询有结果！");
                }
                while (rs.next()) {
                    reservationBean reservation = new reservationBean();
                    reservation.setId(rs.getInt("id"));
                    reservation.setUsername(rs.getString("username"));
                    reservation.setState(rs.getString("state"));
                    reservation.setMeirongshi_name(rs.getString("meirongshi_name"));
                    reservation.setServiceName(rs.getString("serviceName"));
                    reservation.setService_date(rs.getString("service_date"));
                    reservation.setService_period(rs.getString("service_period"));
                    reservation.setUser_tel(rs.getString("user_tel"));
                    reservation.setRemark(rs.getString("remark"));
                    reservation.setFullname(rs.getString("fullName"));
                    list.add(reservation);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        else {
            System.out.println("数据库连接获取失败！");
        }
        return list;
    }

    public List<reservationBean> getReserByUserId(int userId) throws SQLException {
        List<reservationBean> list = new ArrayList<>();
        Connection connection = DataBaseutil.getConnection();
        if(connection != null) {
            Statement statement = connection.createStatement();
            String sql = """
    SELECT a.*,b.name AS meirongshi_name, s.name AS serviceName
    FROM appointment a
    JOIN service s ON a.service_id = s.id
    JOIN beau b ON a.beau_id = b.id
    WHERE a.user_id = ?
    """;
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                reservationBean reservation = new reservationBean();

                // 假设数据库表中的列名和reservationBean类中的属性名一致
                reservation.setId(rs.getInt("id"));
                reservation.setUser_id(rs.getInt("user_id"));
                reservation.setServiceName(rs.getString("serviceName"));
                reservation.setState(rs.getString("state"));
                reservation.setFullname(rs.getString("fullname"));
                reservation.setMeirongshi_name(rs.getString("meirongshi_name"));
                reservation.setService_date(String.valueOf(rs.getDate("service_date")));
                reservation.setService_period(rs.getString("service_period"));
                reservation.setUser_tel(rs.getString("user_tel"));
                reservation.setRemark(rs.getString("remark"));
                reservation.setService_id(rs.getInt("service_id"));
                // 将对象添加到列表中
                list.add(reservation);
            }

        }
        return list;
    }

    public boolean updateState(int reservationId, String state) throws SQLException {
        String sql = "UPDATE appointment SET state = ? WHERE id = ?";
        Connection connection = DataBaseutil.getConnection();
        if (connection != null) {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, state);
            ps.setInt(2, reservationId);
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        }
        return false;
    }

    public boolean delete(int reservationId) throws SQLException {
        String sql = "DELETE FROM appointment WHERE id = ?";
        Connection connection = DataBaseutil.getConnection();
        if (connection != null) {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, reservationId);
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        }
        return false;
    }

    public boolean insert(reservationBean reservation) throws SQLException {
        String sql = "INSERT INTO appointment(fullname,user_tel,service_id,beau_id,service_date,service_period,remark,user_id) VALUES(?,?,?,?,?,?,?,?)";
        Connection connection = DataBaseutil.getConnection();
        if (connection != null) {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, reservation.getFullname());
            ps.setString(2, reservation.getUser_tel());
            ps.setInt(3, reservation.getService_id());
            ps.setInt(4, reservation.getBeau_id());
            ps.setString(5,reservation.getService_date());
            ps.setString(6, reservation.getService_period());
            ps.setString(7, reservation.getRemark());
            if(reservation.getUser_id()==null){
                ps.setNull(8,java.sql.Types.INTEGER);
            }
            else{
                ps.setInt(8, reservation.getUser_id());
            }
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        }
        return false;
    }
    public List<reservationBean> search(String search) throws SQLException {
        System.out.println(search);
        List<reservationBean> list = new ArrayList<>();
        Connection connection = DataBaseutil.getConnection();
        if(connection != null) {
            Statement statement = connection.createStatement();
            String sql = """
                        SELECT 
                            a.id,
                            u.username,
                            a.state,
                            b.name AS meirongshi_name,
                            s.name AS serviceName, -- 从 service 表中获取 name 列
                            a.service_date,
                            a.service_period,
                            a.user_tel,
                            a.remark,
                            a.fullName,
                            a.user_id
                        FROM 
                            appointment a
                        LEFT JOIN 
                            user u ON a.user_id = u.uid
                        LEFT JOIN 
                            service s ON a.service_id = s.id -- 使用 service_id 关联 service 表
                        LEFT JOIN 
                            beau b ON a.beau_id = b.id
                        WHERE 
                            a.fullName LIKE ? OR a.user_tel LIKE ?;
                        """;

            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, "%" + search + "%");
            stmt.setString(2, "%" + search + "%");

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                reservationBean reservation = new reservationBean();
                reservation.setId(rs.getInt("id"));
                reservation.setUsername(rs.getString("username"));

                reservation.setUser_id(rs.getInt("user_id"));
                reservation.setServiceName(rs.getString("serviceName"));
                reservation.setState(rs.getString("state"));
                reservation.setFullname(rs.getString("fullName"));
                reservation.setMeirongshi_name(rs.getString("meirongshi_name"));
                reservation.setService_date(String.valueOf(rs.getDate("service_date")));
                reservation.setService_period(rs.getString("service_period"));
                reservation.setUser_tel(rs.getString("user_tel"));
                reservation.setRemark(rs.getString("remark"));
                list.add(reservation);
            }
        }

        return list;
    }

    public void batchUpdateState(List<Integer> ids, String action, List<Integer> successIds, List<Integer> failedIds) throws SQLException {
        String sql = "UPDATE appointment SET state = ? WHERE id = ?";

        try (Connection connection = DataBaseutil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            connection.setAutoCommit(false);  // 开启事务，禁用自动提交

            for (Integer id : ids) {
                preparedStatement.setString(1, action);
                preparedStatement.setInt(2, id);
                preparedStatement.addBatch();  // 添加到批处理
            }

            int[] result = preparedStatement.executeBatch();  // 执行批量操作
            for (int i = 0; i < result.length; i++) {
                if (result[i] == 1) {
                    successIds.add(ids.get(i));  // 成功更新
                } else {
                    failedIds.add(ids.get(i));  // 更新失败
                }
            }

            connection.commit();  // 提交事务

        } catch (SQLException e) {
            e.printStackTrace();
            throw e;  // 抛出异常，事务会回滚
        }
    }

    public void batchDeleteReservation(List<Integer> ids, List<Integer> successIds, List<Integer> failedIds) throws SQLException {
        String sql = "DELETE FROM appointment WHERE id = ?";

        // 确保事务提交与回滚管理
        try (Connection connection = DataBaseutil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            connection.setAutoCommit(false);  // 开启事务，禁用自动提交

            // 批量添加删除操作
            for (Integer id : ids) {
                preparedStatement.setInt(1, id);
                preparedStatement.addBatch();  // 将删除操作添加到批处理
            }

            // 执行批量删除
            int[] result = preparedStatement.executeBatch();  // 执行批量操作

            // 处理批量执行结果
            for (int i = 0; i < result.length; i++) {
                // 检查每个操作是否成功
                if (result[i] == 1) {  // 执行删除成功
                    successIds.add(ids.get(i));  // 记录成功删除的 id
                } else {  // 执行删除失败
                    failedIds.add(ids.get(i));  // 记录失败的 id
                }
            }

            // 提交事务
            connection.commit();  // 提交事务

        } catch (SQLException e) {
            e.printStackTrace();
            throw e;  // 发生异常时，事务会回滚
        }
    }
}